libname hcup2009 '.';
libname sidwork '.';

proc sql;
create table sidwork.totalallbystate as select 
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(Chronic) as HCUPChronic,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(pregchild) as pregchild,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
FIPS as FIPS
from sidwork.sid2009
group by bmonth, byear, FIPS;
quit;

data sidwork.totalallbystate;
set sidwork.totalallbystate;
if preg=. then preg=0;
if count=. then count=0;
run;


proc sql;
create table sidwork.totalblackbystate as select 
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(Chronic) as HCUPChronic,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
FIPS as FIPS
from sidwork.sid2009
where race=2 
group by bmonth, byear, FIPS;
quit;

data sidwork.totalblackbystate;
set sidwork.totalblackbystate;
if preg=. then preg=0;
if count=. then count=0;
run;

proc sql;
create table sidwork.totalnotblackbystate as select 
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(Chronic) as HCUPChronic,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(count) as count,
sum(mental) as mental,
sum(injury) as injury,
sum(fracture) as fracture,
sum(preg) as preg,
bmonth as bmonth,
byear as byear,
FIPS as FIPS
from sidwork.sid2009
where race NE 2 
group by bmonth, byear, FIPS;
quit;

data sidwork.totalnotblackbystate;
set sidwork.totalnotblackbystate;
if preg=. then preg=0;
if count=. then count=0;
run;



************Total, not aggregated by state;


proc sql;
create table sidwork.totalall as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,
sum(freecare) as freecare,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
group by bmonth, byear;
quit;

data sidwork.totalall;
set sidwork.totalall;
if preg=. then preg=0;
if count=. then count=0;
run;


proc sql;
create table sidwork.totalblack as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,
sum(freecare) as freecare,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race=2 
group by bmonth, byear;
quit;

data sidwork.totalblack;
set sidwork.totalblack;
if preg=. then preg=0;
if count=. then count=0;
run;

proc sql;
create table sidwork.totalnotblack as select 
sum(public) as public,
sum(private) as private,
sum(self) as self,
sum(freecare) as freecare,

sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race NE 2 
group by bmonth, byear;
quit;


data sidwork.totalnotblack;
set sidwork.totalnotblack;
if preg=. then preg=0;
if count=. then count=0;
run;


**************Only low income zipcodes;

proc sql;
create table sidwork.totalallli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;

data sidwork.totalallli;
set sidwork.totalallli;
if preg=. then preg=0;
if count=. then count=0;
run;


proc sql;
create table sidwork.totalblackli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race=2 and ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;

data sidwork.totalblackli;
set sidwork.totalblackli;
if preg=. then preg=0;
if count=. then count=0;
run;

proc sql;
create table sidwork.totalnotblackli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race NE 2 and ZIPINC_QRTL LT 2
group by bmonth, byear;
quit;


data sidwork.totalnotblackli;
set sidwork.totalnotblackli;
if preg=. then preg=0;
if count=. then count=0;
run;


***Pull 2009 data using only states available in 1999 (Appendix Table 4);

data states99;
input FIPS 2.;
datalines; 
4
15
19
24
36
41
15
49
55
;
run;

proc sql;
create table sidwork.totalblack99states as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race=2 and FIPS in (select FIPS from states99)
group by bmonth, byear;
quit;


data sidwork.totalblack99states;
set sidwork.totalblack99states;
if preg=. then preg=0;
if count=. then count=0;
run;


proc sql;
create table sidwork.total99states as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where FIPS in (select FIPS from states99)
group by bmonth, byear;
quit;

data sidwork.total99states;
set sidwork.total99states;
if preg=. then preg=0;
if count=. then count=0;
run;

proc sql;
create table sidwork.totalnotblack99states as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from sidwork.sid2009
where race NE 2 and FIPS in (select FIPS from states99)
group by bmonth, byear;
quit;

data sidwork.totalnotblack99states;
set sidwork.totalnotblack99states;
if preg=. then preg=0;
if count=. then count=0;
run;



